<?php

declare(strict_types=1);

namespace App\CustHub\Mapper;

use App\CustHub\Model\ChRoomCodeRecord;
use App\CustHub\Model\ChRoomCodeRoomRelation;
use Carbon\Carbon;
use Mine\Abstracts\AbstractMapper;
use function Hyperf\Collection\collect;

class ChRoomCodeRecordMapper extends AbstractMapper
{
    public function assignModel()
    {
        $this->model = ChRoomCodeRecord::class;
    }

    public function getCodeStatisticsByUid(int $uid,array $codeIds)
    {
        $time = Carbon::now();

        // 确保 code_id 类型一致（根据实际数据库字段类型调整）
        $codeIds = array_filter(array_map('intval', $codeIds)); // 改为整型转换

        $query = $this->model::query()->where('uid', $uid);

        // 执行原始查询
        $collection = $query
            ->selectRaw('
            code_id,
            COUNT(CASE WHEN user_id is null THEN 1 END) as total_count,
            COUNT(CASE WHEN is_enter_room = 1 THEN 1 END) as entered_total_count,
            COUNT(CASE WHEN DATE(created_at) = ? THEN 1 END) as daily_count,
            COUNT(CASE WHEN DATE(created_at) = ? AND is_enter_room = 1 THEN 1 END) as entered_daily_count
        ', [$time->toDateString(), $time->toDateString()])
            ->when(!empty($codeIds), function ($q) use ($codeIds) {
                $q->whereIn('code_id', $codeIds);
            })
            ->groupBy('code_id')
            ->get();

        // 构建默认数据结构
        $defaultStats = [
            'total_count' => 0,
            'entered_total_count' => 0,
            'daily_count' => 0,
            'entered_daily_count' => 0
        ];

        // 合并结果（保证所有 code_id 都有值）
        return collect($codeIds)->mapWithKeys(function ($codeId) use ($collection, $defaultStats) {
            $stats = $collection->firstWhere('code_id', $codeId);
            return [
                $codeId => $stats ? [
                    'total_count' => (int)$stats->total_count,
                    'entered_total_count' => (int)$stats->entered_total_count,
                    'daily_count' => (int)$stats->daily_count,
                    'entered_daily_count' => (int)$stats->entered_daily_count
                ] : $defaultStats
            ];
        });
    }

    public function getCodeStatisticsByRoomIds(int $uid, array $roomIds)
    {
        if (empty($roomIds)) return [];

        $results = $this->model::query()
            ->selectRaw('
            room_id,
            COUNT(*) as total_count,
            COUNT(CASE WHEN is_enter_room = 1 THEN 1 END) as entered_total_count
        ')
            ->where('uid', $uid)
            ->whereIn('room_id', $roomIds)
            ->groupBy('room_id')
            ->get()
            ->keyBy('room_id');

        return collect($roomIds)->mapWithKeys(function ($roomId) use ($results) {
            return [
                $roomId => [
                    'total_count' => (int)($results[$roomId]->total_count ?? 0),
                    'entered_total_count' => (int)($results[$roomId]->entered_total_count ?? 0)
                ]
            ];
        })->toArray();
    }

    public function getDailyCodeStatisticsByRoomRelation(int $relationId, array $timeRange)
    {
        return ChRoomCodeRoomRelation::query()
            ->from('ch_room_code_room_relation as relation')
            ->selectRaw('
            DATE(records.created_at) as date,
            COUNT(DISTINCT CASE WHEN relation.is_new_create = 1 THEN relation.room_id END) as new_rooms,
            COUNT(CASE WHEN records.is_enter_room = 1 THEN 1 END) as enter_room_count,
            relation.total_visit as total_count,
            COUNT(CASE WHEN records.is_out_room = 1 THEN 1 END) as out_room_count
        ')
            ->leftJoin('ch_room_code_record as records', function ($join){
                $join->on('records.relation_id', '=', 'relation.id');
            })
            ->havingRaw('date is not null')
            ->where('relation.id', $relationId)
            ->when(!empty($timeRange), function ($query) use ($timeRange) {
                $query->whereBetween('records.created_at', [
                    Carbon::parse($timeRange[0])->startOfDay(),
                    Carbon::parse($timeRange[1])->endOfDay()
                ]);
            })
            ->groupBy('date')
            ->orderByDesc('date');
    }
    public function getDailyCodeStatisticsByCodeId(int $uid,int $codeId, array $timeRange)
    {
        return $this->model::query()
            ->selectRaw('
            DATE(records.created_at) as date,
            COUNT(DISTINCT CASE WHEN relation.is_new_create = 1 THEN relation.room_id END) as new_rooms,
            COUNT(CASE WHEN records.user_id is null THEN 1 END) as total_count,
            COUNT(CASE WHEN records.is_enter_room = 1 THEN 1 END) as enter_room_count,
            COUNT(CASE WHEN records.is_out_room = 1 THEN 1 END) as out_room_count
        ')
            ->from('ch_room_code_record as records')
            ->leftJoin('ch_room_code_room_relation as relation', function ($join) use ($codeId) {
                $join->on('records.room_id', '=', 'relation.room_id')
                    ->where('relation.code_id', $codeId);
            })
            ->where('records.uid', $uid)
            ->when(!empty($timeRange), function ($query) use ($timeRange) {
                $query->whereBetween('records.created_at', [
                    Carbon::parse($timeRange[0])->startOfDay(),
                    Carbon::parse($timeRange[1])->endOfDay()
                ]);
            })
            ->groupBy('date')
            ->orderByDesc('date');
    }
    public function getDailyCodeStatisticsByCodeIdAndRoomId(int $uid, int $codeId, int $roomId)
    {
        return $this->model::query()
            ->selectRaw('
            DATE(created_at) as date,
            COUNT(CASE WHEN user_id is null THEN 1 END) as total_count,
            COUNT(CASE WHEN is_enter_room = 1 THEN 1 END) as entered_total_count,
            COUNT(CASE WHEN is_out_room = 1 THEN 1 END) as out_total_count
        ')
            ->where('code_id', $codeId)
            ->where('room_id', $roomId)
            ->where('uid', $uid)
            ->groupBy('date')
            ->orderByDesc('date');
    }

    public function getRoomCodeStatisticsByTime(int $uid, string $time)
    {
        $parsedDate = Carbon::parse($time)->toDateString();

        return $this->model::query()
            ->selectRaw('
            records.code_id,
            code.code_name,
            COUNT(DISTINCT CASE WHEN relation.is_new_create = 1 THEN relation.room_id END) as new_rooms,
            COUNT(CASE WHEN records.user_id is null THEN 1 END) as total_count,
            COUNT(CASE WHEN records.is_enter_room = 1 THEN 1 END) as enter_room_count,
            COUNT(CASE WHEN records.is_out_room = 1 THEN 1 END) as out_room_count
        ')
            ->from('ch_room_code_record as records')
            // 关联活码主表获取名称
            ->leftJoin('ch_room_code as code', 'records.code_id', '=', 'code.id')
            // 关联房间关系表用于判断新创建群
            ->leftJoin('ch_room_code_room_relation as relation', function ($join) {
                $join->on('records.room_id', '=', 'relation.room_id')
                    ->on('records.code_id', '=', 'relation.code_id');
            })
            // 查询条件
            ->where('records.uid', $uid)
            ->whereDate('records.created_at', $parsedDate)
            // 按活码ID分组
            ->groupBy(['records.code_id', 'code.code_name'])
            // 按活码ID升序排列
            ->orderBy('records.code_id');
    }

}